<?php

class Core_Model_DbTable_Products extends YouNet_Db_Table
{    
    const STATUS_DELETE = 0;    
	const STATUS_SHOWN  = 1;
	const STATUS_HIDDEN = 2;
    
	const TABLE_NAME = 'products';
	protected $_name = self::TABLE_NAME;
	protected $_rowClass = 'Core_Model_Product';
	protected $_dependentTables = array(
		'Core_Model_DbTable_Product_Images',
		'Core_Model_DbTable_Product_Shares',
		'Core_Model_DbTable_Product_Views'
	);
	
	static private $_instance;
	public static function getInstance()
	{
		if( !(self::$_instance instanceof Core_Model_DbTable_Products) )
		{
			self::$_instance = new Core_Model_DbTable_Products();
		}
		return self::$_instance;
	}
	
	/**
	 * Get a specific product by its id and id_store
	 * @param int $id_store
	 * @param int $id_product
	 * @return Core_Model_Product
	 */
	public function getProductBelongToStore($id_store, $id_product)
	{
		$select = $this->select()
						->where('id_store=?', $id_store)
						->where('id=?', $id_product);
		return $this->fetchRow($select);
	}

	/**
	 *
	 * @param int $id_page
	 * @param int $id_product
	 * @return Core_Model_Product
	 */
	public function getShareProduct($id_product)
	{
		/*
		$query = $this->select()
				->setIntegrityCheck(false)
				->from(array('p' => self::TABLE_NAME), array('p.*'))
				->join(array('pg' => Core_Model_DbTable_Pages::TABLE_NAME), 'pg.id_store = p.id_store', array())
				->where('pg.id_page=?', $id_page)
				->where('p.id = ?', $id_product);
		 * 
		 */
		return $this->fetchRow(array('id = ?' => $id_product));
	}
    /**
     *
     * @param type $id_page
     * @param type $id_store
     * @param type $id_product
     * @return type 
     */
    public function getProductForNotify($id_page, $id_store, $id_product)
	{
		$query = $this->select()
				->setIntegrityCheck(false)
				->from(array('p' => self::TABLE_NAME), array('p.*'))
                ->join(array('s' => Core_Model_DbTable_Stores::TABLE_NAME), 'p.id_store = s.id', array('email_mods' => "s.notify_email"))
				->join(array('pg' => Core_Model_DbTable_Pages::TABLE_NAME), 'pg.id_store = p.id_store', array('id_page' => "pg.id_page", 'page_name' => 'pg.name'))
				->where('pg.id_page=?', $id_page)
                ->where('s.id=?', $id_store)
				->where('p.id = ?', $id_product);

		return $this->fetchRow($query);
	}
	
	public function findProductsBelongToStore($params, $pageNumber, $arrSort = array(), $item_per_page = 12)
	{
        //Sort products for fanpage list products
        if (!empty($arrSort) && !is_array($arrSort))
        {
            $convertSort = array();
            if($arrSort == 'latest_products')
            {
                $convertSort['time_created'] = 'DESC';
            }
            else if($arrSort == 'price_asc')
            {
                $convertSort['price'] = 'ASC';
            }
            else if($arrSort == 'price_desc')
            {
                $convertSort['price'] = 'DESC';
            }
            $arrSort = $convertSort;
        }
        
		$select = $this->_getSelectionProductsBelongToStore($params, $arrSort);
		if( isset($params['id_page']) )
		{
			$paginator = Store_Paginator::pageFactory($params['id_page'], $select);
			$paginator->setItemCountPerPage($item_per_page);
		}
		else
		{
			$paginator = Zend_Paginator::factory($select);
			$paginator->setItemCountPerPage(ITEM_COUNT_PER_PAGE);
		}
		$paginator->setCurrentPageNumber($pageNumber);
		return $paginator;
	}

	private function _getSelectionProductsBelongToStore($params, $arrSort)
	{
		$select = $this->select()
						->setIntegrityCheck(false)
						->from(array('p' => self::TABLE_NAME), array('p.*'))						
						->where('p.id_store = ?', $params['id_store'])
						->group('p.id');
        
		
		if(!empty($arrSort['product_name']))
		{
			$arrSort['name'] = $arrSort['product_name'];
			unset($arrSort['product_name']);
		}
		
        //Order by
        if(!empty($arrSort))
        {
            foreach ($arrSort as $key => $value)
            {
                $select->order($key.' '.$value);		 
            }
        }
        else
        {
            $select->order('time_created DESC');
        }       
        
        //Status
		if (!empty($params['status']))
		{
			$select->where('status = ?',$params['status']);
		}
        else
        {
            $select->where('status != ?',  self::STATUS_DELETE);
        }
        
		if(!empty($params['id_category']))
		{
			$select->join(array('cc' => Core_Model_DbTable_Category_Customs::TABLE_NAME), 'cc.id = p.id_custom_category', array('cc.name as category_name', 'cc.description as category_description'))
						->where('cc.path LIKE ?', $params['id_category']);
		} else {
            if(!empty($params['category']))
            {
                $select->join(array('cc' => Core_Model_DbTable_Category_Customs::TABLE_NAME), 'cc.id = p.id_custom_category', array('cc.name as category_name', 'cc.description as category_description'))
                            ->where('cc.path LIKE ?', $params['category']);
            }
        }
		
		if (!empty($params['keyword']))
		{
			$keyword = preg_replace(array('/[ ]{2,}/','/%/','/_/'), array(' ', '\%','\_'), $params['keyword']);
			$keyword = str_replace($this->_translate('keyword'), '', $keyword);
			$keyword = trim($keyword);
			if (!empty($keyword))
			{
				$normalKeyword = $keyword . '%'; 
				$keyword = $this->addPlusSignToEachWord($keyword);
				$keyword = $this->_db->quote($keyword);
				$normalKeyword = $this->_db->quote($normalKeyword);
				$select->where("(MATCH(p.name, p.description) AGAINST($keyword IN BOOLEAN MODE) OR (p.sku LIKE $normalKeyword))");
			}
		}
		return $select;
	}

	public function getCustomCategory($id_product)
	{
		$select = $this->select()->setIntegrityCheck(false)
						->from(array('p' => self::TABLE_NAME), array('p.*'))
						->join(array('cc' => Core_Model_DbTable_Category_Customs::TABLE_NAME), 'cc.id = p.id_custom_category', array('cc.name', 'cc.description', 'cc.path'))
						->where('p.id = ?', $id_product);
		return $this->fetchAll($select)->current();
	}
	
	/**
	 * For fulltext search boolean mode
	 * We need to add plus sign to each word in 
	 * search phrase to ensure the result have to
	 * contain all the words.
	 * e.g: ('+civil +engineer' IN BOOLEAN MODE)
	 * @param type $string
	 * @return string 
	 */
	public function addPlusSignToEachWord($string)
	{
		$tok = strtok($string, " \n\t");
		$keyword = '';
		while ($tok !== false)
		{
			$keyword .= "+" . $tok . ' ';
			$tok = strtok(" \n\t");
		}
		$keyword = trim($keyword);
		return $keyword;
	}

	public function createFromParams($params)
	{
		foreach(array('id_system_category','id_custom_category') as $column)
		{
			$value = end($params[$column]);
			if( !$value )
			{
				$value = @prev($params[$column]);
			}
			if( $value )
			{
				$params[$column] = $value;
			}
		}
		if( empty($params['time_created']) )
		{
			$params['time_created'] = time();
		}
        if( empty($params['sku']) )
        {
            unset($params['sku']);
        }
        
		if( empty($params['price']) )
        {
            unset($params['price']);
        }
        else
        {
            $params['price'] = round($params['price'], 2);
        }
        
        if( empty($params['old_price']) )
        {
            unset($params['old_price']);
        }
        else
        {
            $params['old_price'] = round($params['old_price'], 2);
        }
		$product = $this->createRow($params);
		return $product;
	}
    
    public function changeStatusByIdProduct($id, $id_store, $status)
    {        
        $data = array(
          'status' => $status
        );  
        $where = array(
            'id = ?'        => $id,
            'id_store = ?'  => $id_store,
			'status <> ?'	=> self::STATUS_DELETE
        );
		
        return $this->update($data, $where);
    }
    public function deleteByIdProduct($id,$id_store)
    {
        $data = array(
			'status'	=> self::STATUS_DELETE,
			'sku'		=> new Zend_Db_Expr('NULL')
        );     
        $where = array(
            'id = ?'    => $id,
            'id_store = ?'  => $id_store
        );
        return $this->update($data, $where);
    }
	
	public function update(array $data, $where)
	{
		$this->_autoInvalidateByCondition($where);
		return parent::update($data, $where);
	}
	public function delete($where)
	{
		$this->_autoInvalidateByCondition($where);
		parent::delete($where);
	}
	
	private function _autoInvalidateByCondition($where)
	{
		$this->_setupPrimaryKey();        
		
		$arrColumns		= array('id','id_store');
		$arrConditions	= array_keys($where);
		$arrValues = array(
			'id'		=> 0,
			'id_store'	=> 0
		);
		
		foreach($arrConditions as $condition)
		{
			foreach($arrColumns as $column)
			{
				if( stripos($condition, $column) !== false )
				{
					if( @in_array($condition[strlen($column)],array(' ', '<', '>', '=', '!') ) )
					{
						$arrValues[$column] = $where[$condition];
						break;
					}
				}
			}
		}
		$this->validateProductCaching($arrValues['id'],$arrValues['id_store']);
	}
	
	public function findVisibleProduct($id_product)
	{
		$query = $this->select()
				->where('status <> ?', self::STATUS_DELETE)
				->where('id = ?', $id_product);
		return $this->fetchRow($query);
	}
	
	public function getRelatedProducts($params, $productCount)
	{
		$query = $this->_getSelectionProductsBelongToStore($params, array());
		$query->limit($productCount, 0);
		return $this->fetchAll($query);
	}
	
	public function validateProductCaching($id_product, $id_store)
	{
		if( !empty($id_store) )
		{
			$key = Core_Cache_IKeys::MODEL_PRODUCT . '_' . $id_store . '_' . $id_product;
			YouNet_Util::getObjectMemCache()->remove($key);
		}
		Store_Paginator::invalidateCacheByIdModel($id_product);
	}
	
	public function validateAllProductsByIdStore($id_store)
	{
		$allProductIds = $this->select()
				->from(self::TABLE_NAME,'id')
				->where('status <> ?', self::STATUS_DELETE)
				->where('id_store = ?', $id_store)
				->query()->fetchAll();
		if( count($allProductIds) )
		{
			foreach($allProductIds as $product)
			{
				$this->validateProductCaching($product['id'],$id_store);
			}
		}
	}
	
	private function _translate($key)
	{
		$translator = Zend_Registry::get('Zend_Translate');
		/* @var $adapter Zend_Translate_Adapter */
		$adapter = $translator->getAdapter();
		return $adapter->translate($key);
	}
}